package org.example.managere.repository.feibiao;

import org.example.managere.entity.feibiao.AmKnowledgeFailure;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Optional;

/**
 * 电机故障信息存储库 - 精简版
 */
@Repository
public interface AmKnowledgeFailureRepository extends JpaRepository<AmKnowledgeFailure, Integer> {
    
    /**
     * 根据ID查询故障记录（使用连接查询以解决懒加载问题）
     */
    @Query("SELECT f FROM AmKnowledgeFailure f JOIN FETCH f.motorId WHERE f.id = :id")
    Optional<AmKnowledgeFailure> findByIdWithMotor(@Param("id") Integer id);
    
    /**
     * 查询所有故障记录并带上电机信息（使用连接查询以解决懒加载问题）
     * 注意：此方法不适用于分页查询
     */
    @Query("SELECT f FROM AmKnowledgeFailure f JOIN FETCH f.motorId")
    List<AmKnowledgeFailure> findAllWithMotors();
    
    /**
     * 分页查询故障记录（处理懒加载问题）
     * 注意：由于Hibernate的限制，必须先获取ID列表再进行JOIN FETCH
     */
    @Query(value = "SELECT f FROM AmKnowledgeFailure f WHERE f.id IN :ids",
           countQuery = "SELECT COUNT(f) FROM AmKnowledgeFailure f")
    Page<AmKnowledgeFailure> findByIdInWithPagination(@Param("ids") List<Integer> ids, Pageable pageable);
    
    /**
     * 根据电机ID查询故障记录
     */
    List<AmKnowledgeFailure> findByMotorId_MotorId(String motorId);
    
    /**
     * 查询未修复的故障记录
     */
    List<AmKnowledgeFailure> findByRepairTimeIsNull();
    
    /**
     * 根据关键字搜索
     */
    @Query("SELECT f FROM AmKnowledgeFailure f WHERE " +
           "f.failureDescription LIKE %:keyword% OR " +
           "f.failureCause LIKE %:keyword% OR " +
           "f.repairMeasures LIKE %:keyword%")
    Page<AmKnowledgeFailure> searchByKeyword(@Param("keyword") String keyword, Pageable pageable);
    
    /**
     * 统计未修复故障的数量
     */
    long countByRepairTimeIsNull();
    
    /**
     * 查询平均修复时间（小时）- 使用原生SQL查询
     */
    @Query(value = "SELECT AVG(TIMESTAMPDIFF(HOUR, discovery_time, repair_time)) " +
           "FROM am_knowledge_failure WHERE repair_time IS NOT NULL", 
           nativeQuery = true)
    Double findAverageRepairEfficiency();
} 